package ks.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import ks.po.Teaching;

public class TeachingDAO
{
    public List<Teaching> getTeachingsByKidId(int kidId) throws Exception
    {
        List<Teaching> teachings = new ArrayList<Teaching>();
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try
        {
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection("jdbc:mysql://localhost:3306/db_ks", "root", "root");
//            ps = con.prepareStatement("select teaching.*,teacher.name from t_teaching as teaching inner join t_teacher as teacher on (teaching.teacherId=teacher.id) where teaching.kidId=?");
            ps = con.prepareStatement("select t_teacher.name, t_teaching.*\n" +
                    " from t_teacher, t_teaching\n" +
                    " where t_teacher.id = t_teaching.teacherId\n" +
                    " and   t_teaching.kidId=?");
            ps.setInt(1,kidId);
            rs = ps.executeQuery();
            while(rs.next())
            {
                Teaching teaching=new Teaching();
                teaching.setId(rs.getInt("id"));
                teaching.setKidId(kidId);
                teaching.setTeacherId(rs.getInt("teacherId"));
                teaching.setTeachingdate(rs.getString("teachDate"));
                teaching.setEffect(rs.getString("effect"));
                teaching.setTreatment(rs.getString("treatment"));
                teaching.setTeacherName(rs.getString("name"));
                teachings.add(teaching);
            }
        }
        catch (Exception e)
        {
            e.printStackTrace();
            throw new Exception("数据库访问出现异常:" + e);
        }
        finally
        {
            if(rs!=null)
            {
                rs.close();
            }
            if (ps != null)
            {
                ps.close();
            }
            if (con != null)
            {
                con.close();
            }
        }
        return teachings;
    }

    public void save(Teaching teaching) throws Exception
    {
        Connection con = null;
        PreparedStatement ps = null;
        try
        {
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection("jdbc:mysql://localhost:3306/db_ks","root", "root");// 协议://域名(ip):端口/资源（数据库名）

//            ps = con.prepareStatement("insert into t_teaching value(null,?,?,CURDATE(),?,?)");
            ps = con.prepareStatement("insert into t_teaching value(null,?,?,NOW(),?,?)");
            ps.setInt(1, teaching.getKidId());
            ps.setInt(2, teaching.getTeacherId());
            ps.setString(3, teaching.getEffect());
            ps.setString(4, teaching.getTreatment());

            ps.executeUpdate();
        }
        catch (ClassNotFoundException e)
        {
            e.printStackTrace();
            throw new Exception("找不到驱动:" + e.getMessage());// 异常不能在底层丢失了
        }
        catch (SQLException e)
        {
            e.printStackTrace();
            throw new Exception("数据库操作错误:" + e.getMessage());
        }
        finally
        {
            if (ps != null)
            {
                ps.close();
            }
            if (con != null)
            {
                con.close();
            }
        }
    }
}

